package com.ibm.spatiotemp.server.framework.processor.ifx;

import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.sql.Timestamp;
import java.util.ArrayList;
import java.util.Iterator;
import java.util.LinkedList;

import org.apache.log4j.Logger;

import com.fasterxml.jackson.databind.JsonNode;
import com.ibm.spatiotemp.server.framework.request.Request;
import com.ibm.spatiotemp.tools.Constants;
import com.ibm.spatiotemp.tools.CoreMessages;
import com.ibm.spatiotemp.tools.RegionBBox;
import com.ibm.spatiotemp.tools.STException;
import com.ibm.spatiotemp.tools.Util;
import com.ibm.json.java.JSONArray;
import com.ibm.json.java.JSONObject;
import com.ibm.spatiotemp.tools.TrajectoryObject;
import com.ibm.spatiotemp.tools.STSPoint;
import com.ibm.spatiotemp.utils.ConfigManager;

public class IfxTrajectoryService {
	public final static Logger logger = Logger.getLogger(IfxTrajectoryService.class);
	public final static String TYPE = "MultiLineString";
	
	public JSONArray getTrajectory(Connection conn, Request request) throws SQLException, Exception {
		String name = null;
		String value = null;
		JsonNode bodyNode = request.getJnode();
		JsonNode idNode = bodyNode.get("id");
		if (idNode != null) {
			name = "id";
			value = idNode.asText();
		} else if (bodyNode.get("key") != null) {
			name = "key";
			value = bodyNode.get("key").asText();
		}
		if (name == null) {
			return queryTrajectories(conn, request);
		} else {
			return queryTrajectory(conn, request, name, value);
		}
	}
	
	protected JSONArray queryTrajectory(Connection conn, Request request, String name, String value) throws SQLException, STException {
		Statement st = null;
		String id = null;
		String key = null;
		String sqlString = null;
		ResultSet resultSet = null;
		JSONArray jsonArray = null;
		JSONObject json = null;
		ArrayList<TrajectoryObject> trajectoryArray = null;
		TrajectoryObject trajectory = null;	
		String bboxString = null;
		long time1= 0;
		long time2, usedtime;
		
		JsonNode bodyNode = request.getJnode();		
		st = conn.createStatement();
		
		// find the id and key		
		if (name.equalsIgnoreCase("id")) {
			id = value;
			key = null;
		} else {
			sqlString = "select " + Constants.IDCOLUMN_NAME + ", " + Constants.KEYCOLUMN_NAME + " from " + Constants.TABLE_NAME + " where " + Constants.KEYCOLUMN_NAME + " = \'" + value + "\'";
			resultSet = st.executeQuery(sqlString);	
			if (resultSet.next()) {
				id = resultSet.getString(1).trim();
				key = resultSet.getString(2);
				if (key != null) {
					key = key.trim();
				}
			} else {
				throw new STException(String.format(CoreMessages.STHS0013E, name, value));
			}
			resultSet.close();	
		}
		
		String temp = bodyNode.get("start").asText();
		temp = temp.replaceFirst("T", " ");
		String startTime = temp;
		String start = "\'" + temp + "\'";
		temp = bodyNode.get("end").asText();
		temp = temp.replaceFirst("T", " ");
		String endTime = temp;
		String end = "\'" + temp + "\'";
		
		// check bboxRel
		JsonNode bboxNode = bodyNode.get("bbox");
		JsonNode bboxrelNode = bodyNode.get("bboxRel");
		if (bboxNode != null && bboxrelNode != null) {	
			RegionBBox bbox = new RegionBBox(bboxNode);
			String bboxRel = bboxrelNode.asText();
			if ( bboxRel.equalsIgnoreCase("intersect")) {
				bboxString = "sts_trajectoryintersect(\'" + Constants.TABLE_NAME + "\', " + Constants.IDCOLUMN_NAME 
					+ ", \'" + Constants.TSCOLUMN_NAME + "\', " 
					+ start + ", " + end + ", " + bbox.getRegionString() + ", 0)";
			} else if (bboxRel.equalsIgnoreCase("cross")) {
				bboxString = "sts_trajectorycross(\'" + Constants.TABLE_NAME + "\', " + Constants.IDCOLUMN_NAME 
						+ ", \'" + Constants.TSCOLUMN_NAME + "\', " 
						+ start + ", " + end + ", " + bbox.getRegionString() + ", 0)";
			} else if (bboxRel.equalsIgnoreCase("within")) {
				bboxString = "sts_trajectorywithin(\'" + Constants.TABLE_NAME + "\', " + Constants.IDCOLUMN_NAME 
						+ ", \'" + Constants.TSCOLUMN_NAME + "\', " 
						+ start + ", " + end + ", " + bbox.getRegionString() + ", 0)";
			}
			// check relation
			sqlString = "select " + bboxString + " from " + Constants.TABLE_NAME + " where " + Constants.IDCOLUMN_NAME + "=\'" + id + "\'";
			if (ConfigManager.DEBUG_MODE)
				time1 = System.currentTimeMillis();
			resultSet = st.executeQuery(sqlString);				
			if (resultSet.next()) {
				if (!resultSet.getBoolean(1)) {
					return null;
				}
			} else {
				return null;
			}
			if (ConfigManager.DEBUG_MODE) {
				time2 = System.currentTimeMillis();
				usedtime = time2 - time1;
				logger.info("usedtime=" + usedtime + " to check bboxRel sql = " + sqlString);
			}
		}
		
		try {		
			trajectoryArray = getTrajectory(st, startTime, endTime, id);
			if (ConfigManager.DEBUG_MODE)
				time1 = System.currentTimeMillis();
			if (trajectoryArray != null) {
				jsonArray = new JSONArray();
				int number = trajectoryArray.size();
				json = new JSONObject();
				json.put("id", id);
				if (key != null) {
					json.put("key", key);
				}
				if (number==1) {
					for (int i=0; i<number; i++) {						
						trajectory = trajectoryArray.get(i);
						json.put("trajectory", trajectory.toJSONObject());
						jsonArray.add(json);
					}
				} else {
					JSONArray coordsArray = new JSONArray();
					JSONArray timesArray = new JSONArray();
					for (int i=0; i<number; i++) {						
						trajectory = trajectoryArray.get(i);
						coordsArray.add(trajectory.getCoordinates());
						timesArray.add(trajectory.getTimes());
					}
					JSONObject json1 = new JSONObject();
					json1.put("type", TYPE);
					json1.put("coordinates", coordsArray);
					json1.put("times", timesArray);
					json.put("trajectory",json1);
					jsonArray.add(json);
				}
			}
			if (ConfigManager.DEBUG_MODE) {
				usedtime = System.currentTimeMillis() - time1;
				logger.info("usedtime=" + usedtime + " to generate json");	
			}
			st.close();
			return jsonArray;
		} catch (SQLException e1) {
			throw e1;
		} finally {
			try {
				st.close();
			} catch (SQLException e1) {}
		}
	}
	
	protected JSONArray queryTrajectories(Connection conn, Request request) throws SQLException, STException {
		Statement st = null;
		String id = null;
		String key = null;
		String sqlString = null;
		ResultSet resultSet = null;
		JSONArray jsonArray = null;
		JSONObject json = null;
		ArrayList<TrajectoryObject> trajectoryArray = null;
		TrajectoryObject trajectory = null;
		String format = null;
		LinkedList<String> idList = new LinkedList<String>();
		int number;
		long time1=0;
		long usedtime;
		
		st = conn.createStatement();
		
		try {
			JsonNode bodyNode = request.getJnode();		
			String temp = bodyNode.get("start").asText();
			temp = temp.replaceFirst("T", " ");
			String startTime = temp;
			String start = "\'" + temp + "\'";
			temp = bodyNode.get("end").asText();
			temp = temp.replaceFirst("T", " ");
			String endTime = temp;
			String end = "\'" + temp + "\'";
			
			// check bboxRel
			JsonNode bboxNode = bodyNode.get("bbox");
			int cursor = 0;
			if (bboxNode != null) {
				RegionBBox bbox = new RegionBBox(bboxNode);
				// find all objects their trajectories [startTime, endTime] intersect with bbox	
				format = "select * from table(%s(\'%s\', \'%s\', %s, %s, %s, %f)::set(lvarchar not null))(modid)";
				sqlString = String.format(format, Constants.GET_INTERSECT_SET, Constants.TABLE_NAME, Constants.TSCOLUMN_NAME, start, end, bbox.getRegionString(), 0.0);
//				logger.info("select modid=" + sqlString);
				if (ConfigManager.DEBUG_MODE)
					time1 = System.currentTimeMillis();
				try {
					resultSet = st.executeQuery(sqlString);	
					while (resultSet.next()) {
						temp = resultSet.getString(1).trim();
						if (temp != null) {
							idList.add(temp);
						}
					}
					resultSet.close();
				} catch (SQLException e) {
					if (e.getErrorCode() == -9976) {
						logger.info("there is no ids to return");
						st.close();
						return null;
					} else {
						throw e;
					}
				}
				if (ConfigManager.DEBUG_MODE) {
					usedtime = System.currentTimeMillis() - time1;
					logger.info("usedtime=" + usedtime + " to get all bbox ids sqlString=" + sqlString);
				}
			} else {
				// find all objects their trajectories [startTime, endTime] intersect with bbox		
				format = "select %s from %s;";
				sqlString = String.format(format, Constants.IDCOLUMN_NAME, Constants.TABLE_NAME);
				if (ConfigManager.DEBUG_MODE)
					time1 = System.currentTimeMillis();
				resultSet = st.executeQuery(sqlString);	
				while (resultSet.next()) {
					temp = resultSet.getString(1).trim();
					if (temp != null) {
						idList.add(temp);
					}
				}
				resultSet.close();
				if (ConfigManager.DEBUG_MODE) {
					usedtime = System.currentTimeMillis() - time1;
					logger.info("usedtime=" + usedtime + " to get all ids, cursor=" + cursor);
				}
			}
			logger.info("There are " + idList.size() + " moving objects to check trajectory.");
			jsonArray = new JSONArray();
			// get trajectory one by one,  intersect with bbox
			Iterator<String> iter = idList.iterator();	
			if (ConfigManager.DEBUG_MODE) 
				time1 = System.currentTimeMillis();
			while (iter.hasNext()) {
				id = iter.next();
//				// find the id and key		
//				sqlString = "select " + Constants.KEYCOLUMN_NAME + " from " + Constants.TABLE_NAME + " where " + Constants.IDCOLUMN_NAME + " = \'" + id + "\'";
//				resultSet = st.executeQuery(sqlString);	
//				if (resultSet.next()) {
//					key = resultSet.getString(1);
//					if (key != null) {
//						key = key.trim();
//					}
//				} else {
//					key = null;
//				}
//				resultSet.close();					
				trajectoryArray = getTrajectory(st, startTime, endTime, id);
				if (trajectoryArray != null) {
					number = trajectoryArray.size();
					json = new JSONObject();
					json.put("id", id);
					if (key != null) {
						json.put("key", key);
					}
					if (number==1) {
						for (int i=0; i<number; i++) {						
							trajectory = trajectoryArray.get(i);
							json.put("trajectory", trajectory.toJSONObject());
							jsonArray.add(json);
						}
					} else {
						JSONArray coordsArray = new JSONArray();
						JSONArray timesArray = new JSONArray();
						for (int i=0; i<number; i++) {						
							trajectory = trajectoryArray.get(i);
							coordsArray.add(trajectory.getCoordinates());
							timesArray.add(trajectory.getTimes());
						}
						JSONObject json1 = new JSONObject();
						json1.put("type", TYPE);
						json1.put("coordinates", coordsArray);
						json1.put("times", timesArray);
						json.put("trajectory",json1);
						jsonArray.add(json);
					}
				}
			}
			if (ConfigManager.DEBUG_MODE) {
				usedtime = System.currentTimeMillis() - time1;
				logger.info("usedtime=" + usedtime + " to get all trajectories");	
			}
			st.close();
			number = jsonArray.size();
			logger.info("Returned " + number + " trajectories.");
			if (number > 0) {
				return jsonArray;
			} else {		 
				return null;
			}
		} catch (SQLException e1) {
			throw e1;
		} finally {
			try {
				st.close();
			} catch (SQLException e1) {}
		}
	}
	
	
	protected ArrayList<TrajectoryObject> getTrajectory(Statement st, String startTime, String endTime, String id) throws SQLException, STException {
		String sqlString = null;
		ResultSet resultSet = null;
		String timeString = null;
//		String startPos = null;
//		String endPos = null;
//		STSPoint endpoint = null;
//		long startLong;
//		long endLong;
		STSPoint startpoint = null;

		ArrayList<TrajectoryObject> trajectoryArray = null;
		TrajectoryObject	trajectory;
		int firstflag = 0;
		long t, t1;
		double x, x1;
		double y, y1;
		int pointcount;
		int linecount;
		int repeatednumber;
		double x2, y2;		

		String start;
		String end;
		long time1 = 0;
		long time2, usedtime;
		
		try {
			Timestamp.valueOf(startTime).getTime();
		} catch (IllegalArgumentException e) {
			throw new STException(String.format(CoreMessages.STHS0001E, "startTime", startTime));
		}
		start = "\'" + startTime + "\'";
		
		try {
			Timestamp.valueOf(endTime).getTime();
		} catch (IllegalArgumentException e) {
			throw new STException(String.format(CoreMessages.STHS0001E, "endTime", endTime));
		}
		end = "\'" + endTime + "\'";
		
//		sqlString = "select sts_getposition(" + Constants.TSCOLUMN_NAME + ", " + start + ") from "
//				+ Constants.TABLE_NAME + " where " + Constants.IDCOLUMN_NAME + "=\'" + id +"\'";
//		if (ConfigManager.DEBUG_MODE) 
//			time1 = System.currentTimeMillis();
//		resultSet = st.executeQuery(sqlString);
//		if (resultSet.next()) {
//			startPos = resultSet.getString(1);
//			if (startPos != null) {
//				startpoint = new STSPoint(startLong, startPos);
//			}
//		}
//		resultSet.close();
//		if (ConfigManager.DEBUG_MODE) {
//			time2 = System.currentTimeMillis();
//			usedtime = time2 - time1;
//			logger.info("usedtime=" + usedtime + " to get the start position");	
//			time1 = time2;
//		}
//		sqlString = "select sts_getposition(" + Constants.TSCOLUMN_NAME + ", " + end + ") from " 
//				+ Constants.TABLE_NAME + " where " + Constants.IDCOLUMN_NAME + "=\'" + id +"\'";
//		resultSet = st.executeQuery(sqlString);
//		if (resultSet.next()) {
//			endPos = resultSet.getString(1);
//			if (endPos != null) {
//				endpoint = new STSPoint(endLong, endPos);
//			}
//		}
//		if (ConfigManager.DEBUG_MODE) {
//			resultSet.close();
//			time2 = System.currentTimeMillis();
//			usedtime = time2 - time1;
//			logger.info("usedtime=" + usedtime + " to get the end position");			
//			time1 = time2;
//		}
		
		trajectoryArray = new ArrayList<TrajectoryObject>();
		trajectory = new TrajectoryObject();
		firstflag = 1;
		pointcount = 0;
		linecount = 0;
		x1 = 0;
		y1 = 0;
		t1 = 0;
		x = 0;
		y = 0;
		t = 0;
		repeatednumber = 0;		
		
		timeString = " and t>=" + start + " and t<=" + end;
		sqlString = "select t, x, y from " + Constants.VTABLE_NAME + " where " + Constants.IDCOLUMN_NAME + "=\'" + id + "\' " + timeString;
//		logger.info("trajectory sql = " + sqlString);
		if (ConfigManager.DEBUG_MODE) 
			time1 = System.currentTimeMillis();
		resultSet = st.executeQuery(sqlString);
		while (resultSet.next()) {
			t = resultSet.getTimestamp(1).getTime();
			x = resultSet.getDouble(2);
			y = resultSet.getDouble(3);			
			if (firstflag == 1) {
				firstflag = 0;
				if (startpoint != null) {
					if(pointcount % ConfigManager.down_sampling_ratio == 0)
					trajectory.addPoint(startpoint);
					pointcount++;
					if ((startpoint.t != t) && ((startpoint.x != x) || (startpoint.y != y))) {
						if(pointcount % ConfigManager.down_sampling_ratio == 0)
							trajectory.addPoint(t,  x,  y);
						pointcount++;
						t1 = t;
						x1 = x;
						y1 = y;
					} else {
						t1 = startpoint.t;
						x1 = startpoint.x;
						y1 = startpoint.y;
					}
				} else {
					if(pointcount % ConfigManager.down_sampling_ratio == 0)
					trajectory.addPoint(t,  x,  y);
					pointcount++;
					t1 = t;
					x1 = x;
					y1 = y;
				}
				continue;
			}
			if (((t-t1)>Constants.ST_TIMEINTERVAL) && ((Util.eluarea(x1, y1, x, y) > Constants.ST_BREAKTRAJ_AREA) || (Util.eludistance(x1, y1, x, y) > Constants.ST_BREAKTRAJ_DIST))) {
	    		/* the trajectory is broken since the area and distance exceed the thresholds */
				/* create a trajectory if pointcount > 1 */
				if (pointcount > 1) {
					trajectoryArray.add(trajectory);					
					linecount++;
				} else if (pointcount == 1 && repeatednumber > 0){
					/* generate a linestring with two similar points */
					x2 = x1 + 0.0000001;
					y2 = y1 + 0.0000001;
					trajectory.addPoint(t1,  x2,  y2);				
					trajectoryArray.add(trajectory);
					linecount++;
				} else {
					/* since the point need be dropped, need to modify length */
//					logger.info("st_getTrajectory(): drop the point since one point can not construct a linestring");					
				}
				/* clear linestring */
				pointcount = 0;				
				repeatednumber = 0;
				trajectory = new TrajectoryObject();
			}
			/* if this is the first one of new linestring or it is not the same as the prev point, add the point into linestring */
			if ((pointcount == 0) || (x1 != x) || (y1!= y)) {
				if(pointcount % ConfigManager.down_sampling_ratio == 0)
				trajectory.addPoint(t,  x,  y);				
				pointcount++;
			} else {
				if (x1 == x && y1 == y) {
					repeatednumber++;
				}
			}
			t1 = t;
			x1 = x;
			y1 = y;
		}		
		resultSet.close();
//		if (endpoint != null && pointcount > 0) {
//			if ((endpoint.t != t) && ((endpoint.x != x) || (endpoint.y != y))) {
//				trajectory.addPoint(endpoint);			
//				pointcount++;
//			}
//		}
	    /* create a linestring if pointcount > 1 */
		if (pointcount > 1) {
			trajectoryArray.add(trajectory);
			linecount++;
		} else if (pointcount == 1 && repeatednumber > 0){
			/* generate a linestring with two similar points */
			x2 = x + 0.0000001;
			y2 = y + 0.0000001;
			trajectory.addPoint(t1, x2, y2);
			trajectoryArray.add(trajectory);
			linecount++;
		}
		if (ConfigManager.DEBUG_MODE) {
			time2 = System.currentTimeMillis();
			usedtime = time2 - time1;
			logger.info("usedtime=" + usedtime + " to get trajectory id=" + id + "output points: "+ pointcount/ConfigManager.down_sampling_ratio);	
		}
		if (linecount > 0) {			
			return(trajectoryArray);
		} else {			
	    	return null;
	    }
	}

}
